# coding:utf-8
from flask import Flask
from flask import jsonify
from flask import request
import json
from flask_cors import CORS
import sqlite3
from flask import g
import os


app = Flask(__name__)
app.debug = True
app.config['JSON_AS_ASCII'] = False
#  r'/*' 是通配符，让本服务器所有的 URL 都允许跨域请求
CORS(app, resources=r'/*')

# configuration
DATABASE = 'room.db'
DEBUG = True
SECRET_KEY = 'development key'
USERNAME = '1'
PASSWORD = '1'

BASE_DIR = os.path.dirname(os.path.abspath(__file__))
db_path = os.path.join(BASE_DIR, DATABASE)


def connect_db():
    return sqlite3.connect(db_path)


def init_db():
    with closing(connect_db()) as db:
        with app.open_resource('schema.sql') as f:
            db.cursor().executescript(f.read())
        db.commit()


@app.before_request
def before_request():
    g.db = connect_db()


@app.after_request
def after_request(response):
    g.db.close()
    return response

# 登录


@app.route('/api/login', methods=['POST'])
def login():
    req = json.loads(request.get_data(as_text=True))
    username = req["username"]
    password = req["password"]
    cur = g.db.execute(
        'select id,username, password, usertype, fullname, number from user order by id desc')
    print(cur)
    all_user = [dict(id=row[0], username=row[1], password=row[2], usertype=row[3], fullname=row[4], number=row[5])
                for row in cur.fetchall()]
    print(all_user)
    for user in all_user:
        if user["username"] == username:
            print('用户：', user["username"], user["usertype"])
            if user["password"] == password:
                data = {
                    "_RejCode": "00000000",
                    "_RejMsg": "success"
                }
                data['id'] = user['id']
                data['username'] = username
                data['usertype'] = user["usertype"]
                data['fullname'] = user["fullname"]
                data['number'] = user["number"]
                return jsonify(data)
            else:
                return jsonify({
                    "_RejCode": "login-error",
                    "_RejMsg": "密码错误"
                })

    return jsonify({
        "_RejCode": "login-error",
        "_RejMsg": "用户不存在"
    })

# 注册


@app.route('/api/register', methods=['POST'])
def add_user():
    req = json.loads(request.get_data(as_text=True))
    username = req["username"]
    password = req["password"]
    number = req["number"]
    usertype = req["usertype"]
    fullname = req["fullname"]
    cur = g.db.execute('select username, password from user order by id desc')
    all_user = [dict(username=row[0], password=row[1])
                for row in cur.fetchall()]
    for user in all_user:
        if user["username"] == username:
            print('用户：', user["username"])
            return jsonify({
                "_RejCode": "add_user-error",
                "_RejMsg": "用户名已存在"
            })
    add_sql = 'insert into user (username, password,number,usertype,fullname) values ("' + \
        username+'","'+password+'","'+number+'","'+usertype+'","'+fullname+'")'
    print(add_sql)
    g.db.execute(add_sql)
    g.db.commit()
    return jsonify({
        "_RejCode": "00000000",
        "_RejMsg": "success"
    })

# 忘记密码-修改密码


@app.route('/api/changePassword', methods=['POST'])
def changePassword():
    req = json.loads(request.get_data(as_text=True))
    username = req["username"]
    newPassword = req["newPassword"]
    number = req["number"]
    all_user_sql = 'select username, number from user order by id desc'
    cur = g.db.execute(all_user_sql)
    all_user = [dict(username=row[0], number=row[1])
                for row in cur.fetchall()]
    for user in all_user:
        if user["username"] == username:
            print('用户：', user["username"])
            if user["number"] == number:
                print('学号:', number)
                conn = sqlite3.connect(db_path)
                # update user set password="qq" where username="aa"
                sql = 'update user set password="'+newPassword+'" where username="'+username+'"'
                print(sql)
                cur = conn.cursor()
                cur.execute(sql)
                return jsonify({
                    "_RejCode": "00000000",
                    "_RejMsg": "success"
                })
            else:
                return jsonify({
                    "_RejCode": "changePassword-error",
                    "_RejMsg": "学号不存在"
                })
    return jsonify({
        "_RejCode": "changePassword-error",
        "_RejMsg": "用户不存在"
    })

# 教学楼查询


@app.route('/api/buildingQ', methods=['POST'])
def buildingQ():
    all_building_sql = 'select id, name from teachbuilding order by id desc'
    cur = g.db.execute(all_building_sql)
    all_building = [dict(id=row[0], name=row[1])
                    for row in cur.fetchall()]
    print(all_building)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["buildings"] = all_building
    return jsonify(data)

# 一栋楼所有教室查询


@app.route('/api/allRoomQ', methods=['POST'])
def allRoomQ():
    req = json.loads(request.get_data(as_text=True))
    building_id = req["buidingId"]
    all_classroom_sql = 'SELECT id,name,seat,storey FROM classroom WHERE building = ' + \
        building_id+' order by id desc;'
    cur = g.db.execute(all_classroom_sql)
    all_classroom = [dict(id=row[0], name=row[1], seat=row[2], storey=row[3])
                     for row in cur.fetchall()]
    print(all_classroom)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["classrooms"] = all_classroom
    return jsonify(data)


# 某层所有教室查询


@app.route('/api/roomQ', methods=['POST'])
def roomQ():
    req = json.loads(request.get_data(as_text=True))
    building_id = req["buidingId"]
    storey_id = req["storeyId"]
    all_classroom_sql = 'SELECT id,name,seat FROM classroom WHERE building = "' + \
        building_id+'" AND storey ="'+storey_id+'" order by id desc;'
    print(all_classroom_sql)
    cur = g.db.execute(all_classroom_sql)
    all_classroom = [dict(id=row[0], name=row[1], seat=row[2])
                     for row in cur.fetchall()]
    print(all_classroom)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["classrooms"] = all_classroom
    return jsonify(data)

# 教学楼新增


@app.route('/api/buildingAdd', methods=['POST'])
def buildingAdd():
    req = json.loads(request.get_data(as_text=True))
    building_name = req["name"]
    add_building = 'INSERT INTO teachbuilding(name) VALUES ("' + \
        building_name+'");'
    print(add_building)
    g.db.execute(add_building)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 教室新增


@app.route('/api/classroomAdd', methods=['POST'])
def classroomAdd():
    req = json.loads(request.get_data(as_text=True))
    name = req["name"]
    seat = req["seat"]
    buildingId = req["buildingId"]
    storeyId = req["storeyId"]
    add_classroom = 'INSERT INTO classroom(name,seat,building,storey) VALUES ("' + \
        name+'","'+seat+'","'+buildingId+'","'+storeyId+'");'
    print(add_classroom)
    g.db.execute(add_classroom)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 用户管理-所有用户查询


@app.route('/api/userQ', methods=['POST'])
def userQ():
    all_student_sql = 'SELECT * FROM user  order by id desc;'
    cur = g.db.execute(all_student_sql)
    all_user = [dict(id=row[0], username=row[1], number=row[3],usertype=row[4],fullname=row[5])
                for row in cur.fetchall()]
    print(all_user)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["users"] = all_user
    return jsonify(data)

# 教学楼信息修改


@app.route('/api/updateBuilding', methods=['POST'])
def updateBuilding():
    req = json.loads(request.get_data(as_text=True))
    building_id = req["id"]
    new_name = req["name"]
    update_building_sql = 'UPDATE teachbuilding SET name = "' + \
        new_name+'" WHERE ID = "'+building_id+'";'
    print(update_building_sql)
    g.db.execute(update_building_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 教学楼删除


@app.route('/api/deleteBuilding', methods=['POST'])
def deleteBuilding():
    req = json.loads(request.get_data(as_text=True))
    building_id = req["id"]
    delete_building_sql = 'DELETE FROM teachbuilding WHERE ID = "'+building_id+'";'
    print(delete_building_sql)
    g.db.execute(delete_building_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 教室信息修改


@app.route('/api/updateRoom', methods=['POST'])
def updateRoom():
    req = json.loads(request.get_data(as_text=True))
    id = req["id"]
    new_name = req["name"]
    new_seat = req["seat"]
    storey_id = req["storeyId"]
    update_room_sql = 'UPDATE classroom set name = "'+new_name + \
        '",seat="'+new_seat+'",storey="'+storey_id+'" WHERE id="'+id+'";'
    print(update_room_sql)
    g.db.execute(update_room_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 教室删除


@app.route('/api/deleteRoom', methods=['POST'])
def deleteRoom():
    req = json.loads(request.get_data(as_text=True))
    room_id = req["id"]
    delete_room_sql = 'DELETE FROM classroom WHERE ID = "'+room_id+'";'
    print(delete_room_sql)
    g.db.execute(delete_room_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 用户信息查询


@app.route('/api/userInforQ', methods=['POST'])
def userInforQ():
    req = json.loads(request.get_data(as_text=True))
    id = req["id"]
    user_infor_sql = 'SELECT id,username,number,fullname FROM user WHERE id = ' + \
        id+' order by id desc;'
    cur = g.db.execute(user_infor_sql)
    user_infor = [dict(id=row[0], username=row[1], number=row[2], fullname=row[3])
                  for row in cur.fetchall()]
    print(user_infor)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["userInfor"] = user_infor
    return jsonify(data)

# 用户删除


@app.route('/api/deleteUser', methods=['POST'])
def deleteUser():
    req = json.loads(request.get_data(as_text=True))
    id = req["id"]
    delete_user_sql = 'DELETE FROM user WHERE ID = "'+id+'";'
    print(delete_user_sql)
    g.db.execute(delete_user_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 申请教室


@app.route('/api/applyRoom', methods=['POST'])
def applyRoom():
    req = json.loads(request.get_data(as_text=True))
    uid = req["uid"]
    applyTime = req["applyTime"]
    appointTime = req["appointTime"]
    buildId = req["buildId"]
    storey = req["storey"]
    roomId = req["roomId"]
    add_apply = 'INSERT INTO applyhistory("uid", "applaytime", "appointtime", "buildid", "storey", "roomid", "state") VALUES ("' + \
        uid+'","'+applyTime+'","'+appointTime+'","' + \
            buildId+'","'+storey+'","'+roomId+'","0");'
    print(add_apply)
    g.db.execute(add_apply)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)

# 用户申请信息查询


@app.route('/api/applyInforQ', methods=['POST'])
def applyInforQ():
    req = json.loads(request.get_data(as_text=True))
    id = req["id"]
    user_infor_sql = 'SELECT * FROM applyhistory WHERE uid="'+id+'";'
    cur = g.db.execute(user_infor_sql)
    apply_infor = [dict(applyTime=row[2], appointTime=row[3], buildId=row[4], storey=row[5], roomId=row[6], state=row[7])
                   for row in cur.fetchall()]
    print(apply_infor)
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    data["applyInfors"] = apply_infor
    return jsonify(data)

# 管理员查用户申请信息


@app.route('/api/allApplyInforQ', methods=['POST'])
def allApplyInforQ():
    req = json.loads(request.get_data(as_text=True))
    queryType = req["queryType"]
    # 查询全部
    if queryType == '':
        user_infor_sql = 'SELECT * FROM applyhistory;'
        cur = g.db.execute(user_infor_sql)
        apply_infor = [dict(id=row[0], uid=row[1], applyTime=row[2], appointTime=row[3], buildId=row[4], storey=row[5], roomId=row[6], state=row[7])
                       for row in cur.fetchall()]
        print(apply_infor)
        data = {
            "_RejCode": "00000000",
            "_RejMsg": "success"
        }
        data["applyInfors"] = apply_infor
        return jsonify(data)
    # 查询全部时间，各种状态的申请信息
    if queryType != '':
        user_infor_sql = 'SELECT * FROM applyhistory WHERE state="'+queryType+'";'
        cur = g.db.execute(user_infor_sql)
        apply_infor = [dict(id=row[0], uid=row[1], applyTime=row[2], appointTime=row[3], buildId=row[4], storey=row[5], roomId=row[6], state=row[7])
                       for row in cur.fetchall()]
        print(apply_infor)
        data = {
            "_RejCode": "00000000",
            "_RejMsg": "success"
        }
        data["applyInfors"] = apply_infor
        return jsonify(data)

    data = {
        "_RejCode": "allApplyInforQ-error",
        "_RejMsg": "查询失败"
    }
    return jsonify(data)

# 管理员审批


@app.route('/api/updateApply', methods=['POST'])
def updateApply():
    req = json.loads(request.get_data(as_text=True))
    id = req["id"]
    state = req["state"]
    update_apply_sql = 'UPDATE applyhistory set state = "'+state+'" WHERE id="'+id+'";'
    print(update_apply_sql)
    g.db.execute(update_apply_sql)
    g.db.commit()
    data = {
        "_RejCode": "00000000",
        "_RejMsg": "success"
    }
    return jsonify(data)


@app.route('/')
def hello_world():
    return 'Hello World!'


if __name__ == '__main__':
    app.run()
